﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("-------= LINQ to SQL=-----");
            Linq1();
            Linq2();
            Linq3(3);
            Linq4();
            Linq5();
            Linq6();
            Linq7();
            Linq8();
            Linq9();
            Linq10();
            Console.ReadLine();
        }

        /// <summary>
        ///  Выведите список городов, где находятся подразделения компании.
        /// </summary>
        public static void Linq1()
        {
            var cities = from city in db.Depts
                         select city.City;

            ///Extensions
            ///var cities = db.Depts.Select(city => city.City);

            Console.WriteLine("-1--------------Cities--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Выведите список имен работников (без повторов, упорядочен по алфавиту).
        /// </summary>
        public static void Linq2()
        {
            var names = (from name in db.Empls
                         orderby name.FirstName
                         select name.FirstName).Distinct();
            ///extensions
            /// var names = db.Empls.OrderBy(a => a.FirstName).Select(name => name.FirstName).Distinct();
            /// variant 2
            /*var namesGroups = from name in db.Empls
                              orderby name.FirstName
                              group name by name.FirstName into nameGroup
                              select nameGroup;*/
            /// variant 2 extensions 
            /// var namesGroups = db.Empls.GroupBy(a => a.FirstName).OrderBy(a => a.Key);
            
            Console.WriteLine("-2--------------Names--------------");
            foreach (var item in names)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Посчитайте, сколько работников у компании в заданном подразделении (номер)
        /// </summary>
        /// <param name="deptId">номер подразделения</param>
        public static void Linq3(int deptId)
        {
            /*var amount = (from name in db.Empls
                         where name.DeptID==deptId
                         select name.FirstName).Count();*/

            var amount = db.Empls.Where(employee => (employee.DeptID == deptId)).Select(name => name.FirstName).Count();

            Console.WriteLine("-3--------------All employees from 3d department--------------");
            Console.WriteLine(amount);
        }

        /// <summary>
        /// Получите список количества работников в каждом подразделении (номер). Упорядочить по убыванию числа работников.
        /// </summary>
        public static void Linq4()
        {
            /*var IDandAmount = from employee in employees
                              group employee by employee.DeptId into dept
                              orderby dept.Count() descending
                              select new { ID = dept.Key, Amount = dept.Count() };*/

            var IDandAmount = db.Empls.Select(empl => empl).GroupBy(emp => emp.DeptID).OrderBy(e => e.Count()).Reverse().Select(item => new { ID = item.Key, Amount = item.Count() });

            Console.WriteLine("-4--------------Department ID | People amount in department--------------");
            foreach (var item in IDandAmount)
            {
                Console.WriteLine("{0} | {1}", item.ID, item.Amount);
            }
        }

        /// <summary>
        /// Получите список количества работников в каждом подразделении (номер). Упорядочить по названиям городов, где располагается подразделение.
        /// </summary>
        public static void Linq5()
        {
            var depts = from employee in db.Empls
                        join department in db.Depts on employee.DeptID equals department.ID
                        group department by department.City into dept
                        orderby dept.Key
                        select new { City = dept.Key, Amount = dept.Count() };

            Console.WriteLine("-5-------------- City | People amount in department--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Выберите список городов, содержащие букву 'Д' в начале слова.
        /// </summary>
        public static void Linq6()
        {
            /*var cities = from city in departments
                         where  city.City.StartsWith("D")
                         select new { City = city.City };*/

            var cities = db.Depts.Where(city => city.City.StartsWith("D")).Select(city => city.City);

            Console.WriteLine("-6--------------Cities started with D--------------");
            foreach (var item in cities)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Выберите список всех городов, содержащие букву 'л' в середине слова и соответствующее количество работников напротив каждого 
        /// названия. Учесть, что в таблице подразделений может быть город, в котором отсутствуют работники, и в таблице работников могут 
        /// быть такие, для которых данные поля DptId = NULL.
        /// </summary>
        public static void Linq7()
        {
            var cities =
                (from department in db.Depts
                 join employee in db.Empls on department.ID equals employee.DeptID into lj
                 from rows in lj.DefaultIfEmpty()
                 where department.City.IndexOf('l') > 0 && department.City.IndexOf('l') < department.City.Length - 1
                 orderby department.City
                 select new { City = department.City, EmplID = rows == null ? 0 : 1 }).GroupBy(a => a.City).Select(item => new { City = item.Key, Amount = item.Sum(a => a.EmplID) });

            Console.WriteLine("-7-------------- City with 'l' in the name | People amount in department-------");
            foreach (var item in cities)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        /// <summary>
        /// Выберите список людей, имеющих однофамильцев. Упорядочить по алфавиту.
        /// </summary>
        public static void Linq8()
        {
            var namesGroups = (from name in db.Empls
                               from name2 in db.Empls
                               where name.ID != name2.ID && name.LastName == name2.LastName
                               orderby name.LastName, name.FirstName
                               select new { ID = name.ID, Name = name.FirstName, Surname = name.LastName }).Distinct();

            Console.WriteLine("8---------------Same SurNames--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Выберите список людей, имеющих несколько полных совпадений имени и фамилии из города 'Львов' и упорядочить по именам.
        /// </summary>
        public static void Linq9()
        {
            var namesGroups = ((from name in db.Empls
                                from name2 in db.Empls
                                where name.ID != name2.ID && name.FirstName == name2.FirstName && name.LastName == name2.LastName && name.DeptID == name2.DeptID
                                orderby name.FirstName
                                select new { ID = name.ID, Name = name.FirstName, Surname = name.LastName, DeptID = name.DeptID }).Where(d => d.DeptID == db.Depts.Where(a => a.City == "Lvov").Select(a => new { ID = a.ID }).First().ID)).Distinct();

            Console.WriteLine("-9--------------Same full names--------------");
            foreach (var item in namesGroups)
            {
                Console.WriteLine(item);
            }
        }

        /// <summary>
        /// Получите список городов с количеством работников с именем 'Василий' более 10. Упорядочить по номеру подразделения.
        /// </summary>
        public static void Linq10()
        {
            var depts = (from employee in db.Empls
                         join department in db.Depts on employee.DeptID equals department.ID
                         where employee.FirstName == "Vasiliy"
                         group department by department.City into dept
                         orderby dept.Key
                         select new { City = dept.Key, Amount = dept.Count() }).Where(d => d.Amount > 10);


            Console.WriteLine("-10-------------- City | People amount in department (Vasiliy)--------------");
            foreach (var item in depts)
            {
                Console.WriteLine("{0} | {1} ", item.City, item.Amount);
            }
        }

        private static string connectionString = @"Data Source=JULIA-PC\SQLEXPRESS;Initial Catalog=employees;Integrated Security=True";
        private static EmployeesDataClassesDataContext db = new EmployeesDataClassesDataContext(connectionString);
    }
}
